﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace FisApp.utility
{

    public static class DBUtils
    {
        //public static string DBConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB.mdf;Database=DB;Integrated Security=True;User Instance=True";
        public static string DBConnectionString = "Data Source=.\\SQLEXPRESS;"
                +"AttachDbFilename =|DataDirectory|\\DB.mdf;"
                +"Initial Catalog=DB;"
                +"Integrated Security=True;"
                +"Connect Timeout=30;"
                +"User Instance=True";


        /// <summary> Returns the results of a SQL Query in the form of a DataTable </summary>
        public static DataSet SQLSelect(string commandText, List<SqlParameter> list)
        {
            SqlConnection sqlConnection = null;
            try
            {
                using (sqlConnection = new SqlConnection(DBConnectionString))
                {
                    sqlConnection.Open();
                    if (sqlConnection.State == ConnectionState.Open) // Eğer bağlantı warsa
                    {
                        using (SqlCommand CommandToExecute = new SqlCommand(commandText, sqlConnection))
                        {
                            if (list != null && list.Count > 0)
                            {
                                CommandToExecute.Parameters.AddRange(list.ToArray());
                            }
                            using (SqlDataAdapter sda = new SqlDataAdapter(CommandToExecute))
                            {
                                using (DataSet ds = new DataSet())
                                {
                                    sda.Fill(ds);
                                    sqlConnection.Close();
                                    return ds;
                                }
                            }
                        }
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary> Executes a SQL Command </summary>
        public static object ExecuteSQLCommand(string commandText, List<SqlParameter> list)
        {
            SqlConnection sqlConnection = null;
            try
            {
                commandText += " SELECT SCOPE_IDENTITY()";
                using (sqlConnection = new SqlConnection(DBConnectionString))
                {
                    sqlConnection.Open();
                    if (sqlConnection.State == ConnectionState.Open) // Eğer bağlantı warsa
                    {
                        //execute command
                        SqlCommand CommandToExecute = new SqlCommand(commandText, sqlConnection);
                        if (list != null && list.Count > 0)
                        {
                            CommandToExecute.Parameters.AddRange(list.ToArray());
                        }
                        object deneme = CommandToExecute.ExecuteScalar();
                        sqlConnection.Close();
                        return deneme;
                    }
                   
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + ((System.Data.SqlClient.SqlException)(e)).ErrorCode);
            }
            finally
            {
                sqlConnection.Close();
            }
            return 0;
        }

        public static SqlParameter GetParameter(string name, object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
            {
                return new SqlParameter(name, DBNull.Value);
            }
            else
            {
                return new SqlParameter(name, value);
            }
        }
    }
}
